-- @owner: wang_zhengyuan
-- @date: 2022/02/11
-- @testpoint: 前置条件-建分区表

--step1:建分区表;expect: 建分区表成功
drop table if exists table_idx_partition_school_003;
 
create table table_idx_partition_school_003(
staff_id int not null,
staff_num real,
higest_degree varchar(80),
graduate_school varchar(64) not null,
graduate_date date,
t_sql_note varchar(70))
with (storage_type=ustore) partition by range(staff_id)
(
partition school1 values less than (36),
partition school2 values less than (1020)
);
 
--step2:创建索引;expect:创建索引成功
drop index if exists idx_in_003_001;
 
create index idx_in_003_001 on table_idx_partition_school_003(staff_id asc,graduate_school,t_sql_note) local (partition school1, partition school2);
 
--step3:创建索引;expect:创建索引成功
drop index if exists idx_in_003_002;
 
create index idx_in_003_002 on table_idx_partition_school_003(to_char(staff_id))local;
 
--step4:创建sequence;expect:创建sequence成功
drop sequence if exists v_seq;
 
create sequence v_seq increment by 1 start with 1;
 
--step5:插入数据;expect:插入数据成功
insert into table_idx_partition_school_003 values(3, 2146.15354309,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_partition_school_003 values(4, 2146.185354309,'doctor','xidian university','2017-07-06 12:00:00',null);
 
insert into table_idx_partition_school_003 values(5, 146.15354309,'master','northwestern polytechnical university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_partition_school_003 values(6, 2134.1535443309,null,'peking university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_partition_school_003 values(7, 2136.3464354309,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_partition_school_003 values(8, 256.1269,'doctor','xidian university','2017-07-06 12:00:00','211');
 
insert into table_idx_partition_school_003 values(10,216.152554309,'doctor','xidian university','2017-07-06 12:00:00',null);
 
insert into table_idx_partition_school_003 values(11,2146.8654309,'master','northwestern polytechnical university','2017-07-06 12:00:00','211&985');
 
insert into table_idx_partition_school_003 values(12,2146.354309,'scholar','peking university','2017-07-06 12:00:00','211&985');
 
begin
    for i in 1..50 loop
    insert into table_idx_partition_school_003 values(mod(i,15),v_seq.nextval/i,'master','northwest a&f university',add_months('2017-03-15 12:00:00',mod(i,5)),'211&985');
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_partition_school_003 values(mod(i,14),v_seq.nextval/i,'undergraduate','northwestern polytechnical university',add_months('2017-03-15 12:00:00',mod(i,8)),'211&985');
    end loop;
end;
/
 
begin
    for i in 1..500 loop
    insert into table_idx_partition_school_003 values(mod(i,300),v_seq.nextval/i,'master','xidian university',add_months('2015-03-15 12:00:00',mod(i,12)),'211');
    end loop;
end;
/
 
--step6:建分区表;expect:建分区表成功
drop table if exists table_idx_in_partition_004;
 
create table table_idx_in_partition_004(
staff_id int primary key,
course_id number,
course_name varchar(500) not null,
higest_degree varchar2(500) not null,
course_start_date date,
course_end_date date,
exam_date date,
score int)
with (storage_type=ustore) partition by range(staff_id)
(partition part1 values less than (5),
partition part2 values less than (10),
partition part3 values less than (maxvalue)
);
 
--step7:创建索引;expect:创建索引成功
drop index if exists idx_in_004_001;
 
create unique index idx_in_004_001 on table_idx_in_partition_004(staff_id,course_id)local;
 
--step8:创建索引;expect:创建索引成功
drop index if exists idx_in_004_002;
 
create unique index idx_in_004_002 on table_idx_in_partition_004(staff_id) local (partition part1, partition part2,partition part3) ;
 
--step9:创建sequence;expect:创建sequence成功
drop sequence if exists v_seq1;
 
create sequence v_seq1 increment by 1 start with 1;
 
--step10:创建sequence;expect:创建sequence成功
drop sequence if exists v_seq2;
 
create sequence v_seq2 increment by 2 start with 1;
 
--step11:插入数据;expect:插入数据成功
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'english','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'grammer','master','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'system','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'language','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'grammer','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'math','doctor','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'sql majorization','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'big data','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
 
insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'performance turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);
 
begin
    for i in 1..300 loop
    insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'english','master',add_months('2018-06-15 12:00:00',mod(i,5)),'2018-06-29 12:00:00','2017-06-25 12:00:00',83+mod(i,15));
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'big data','doctor','2018-06-15 12:00:00',add_months('2018-01-15 12:00:00',i),add_months('2017-06-25 12:00:00',mod(i,11)),86+mod(i,14));
    end loop;
end;
/
 
begin
    for i in 1..300 loop
    insert into table_idx_in_partition_004 values(v_seq1.nextval,v_seq2.nextval,'sql majorization','scholar',add_months('2017-06-15 12:00:00',i-5),add_months('2018-01-16 12:25:00',i),add_months('2017-06-25 12:00:00',mod(i,5)),70+i);
    end loop;
end;
/
 
